package jnpf.base.service.impl;

import jnpf.base.model.dbtable.DbTableUpdate;
import jnpf.database.constant.ViewDataTypeConst;
import jnpf.database.model.DbTableModel;
import jnpf.base.model.dbtable.DbTableCreate;
import jnpf.database.constant.DbConst;
import jnpf.database.enums.DbSttEnum;
import jnpf.database.model.dto.DbConnDTO;
import jnpf.database.model.interfaces.JdbcGetMod;
import jnpf.database.source.impl.DbDM;
import jnpf.database.sql.SqlBase;
import jnpf.database.util.*;
import jnpf.database.source.impl.DbKingbase;
import jnpf.exception.DataException;
import jnpf.base.service.DbTableService;
import jnpf.base.service.DblinkService;
import jnpf.config.ConfigValueUtil;
import jnpf.database.model.DbTableDataForm;
import jnpf.database.model.DbTableFieldModel;
import jnpf.util.*;
import lombok.Cleanup;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.Statement;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 数据管理
 *
 * @author JNPF开发平台组
 * @version V3.1.0
 * @copyright 引迈信息技术有限公司
 * @date 2019年9月27日 上午9:18
 */
@Slf4j
@Service
public class DbTableServiceImpl implements DbTableService {

    @Autowired
    private DblinkService dblinkService;
    @Autowired
    private DataSourceUtil dataSourceUtils;
    @Autowired
    private UserProvider userProvider;
    @Autowired
    private ConfigValueUtil configValueUtil;
    @Autowired
    DbTableService dbTableService;

    @Override
    public List<DbTableModel> getList(String dbId) throws DataException {
        DbConnDTO connDTO = setResource(dbId);
        List<DbTableModel> tableModelList = getListCommon(null, DbTableModel.class, connDTO);
        if(connDTO.getDbBase().getClass() == DbDM.class){
            DbDM.setDmTableModel(setResource(dbId), tableModelList);
        }
        return tableModelList;
    }

    @Override
    public List<DbTableFieldModel> getFieldList(String dbId, String table) throws DataException {
        DbConnDTO connDTO = setResource(dbId);
        List<DbTableFieldModel> listCommon = getListCommon(table, DbTableFieldModel.class, connDTO);
        //根据字段名排序
        Collections.sort(listCommon,new Comparator<DbTableFieldModel>(){
            @Override
            public int compare(DbTableFieldModel o1, DbTableFieldModel o2){
                return o1.getField().compareTo(o2.getField());
            }
        });
        return listCommon;
    }

    @Override
    public boolean isExistByFullName(String dbId, String table, String oldTable) {
        List<DbTableModel> data = null;
        try {
            data = this.getList(dbId).stream().filter(m -> m.getTable().equals(table)).collect(Collectors.toList());
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (!StringUtil.isEmpty(oldTable)) {
            data = data.stream().filter(m -> !m.getTable().equals(oldTable)).collect(Collectors.toList());
        }
        return data.size() > 0;
    }

    /**=======================jdbc查询========================**/

    private <T extends JdbcGetMod>List<T> getListCommon(String table, Class<T> modType, DbConnDTO connDTO) throws DataException{
        try {
            @Cleanup Connection conn = connDTO.getConn();
            //获取查询sql语句
            SqlBase sqlBase = connDTO.getDbBase().getSqlBase();
            String sql = "";
            if(modType == DbTableModel.class){
                sql = sqlBase.getTableListSql(connDTO.getDbSource());
            }else {
                sql = sqlBase.getFieldListSql(table, connDTO.getDbSource());
            }
            //根据sql语句获取List
            return JdbcUtil.queryCustomMods(conn, sql, modType);
        }catch (Exception e){
            throw  new DataException(e.getMessage());
        }
    }


    @Override
    public List<Map<String, Object>> getData(DbTableDataForm dbTableDataForm, String dbId, String table) throws Exception {
        List<Map<String, Object>> list = null;
        DbConnDTO connDTO = setResource(dbId);
        @Cleanup Connection conn = connDTO.getConn();
        if (conn != null) {
            StringBuffer sql = new StringBuffer();
            sql.append(connDTO.getDbBase().getSqlBase().getDataSql(table));
            //模糊查询
            if (!StringUtil.isEmpty(dbTableDataForm.getKeyword()) && !StringUtil.isEmpty(dbTableDataForm.getField())) {
                sql.append(" where " + dbTableDataForm.getField() + " like '%" + dbTableDataForm.getKeyword() + "%'");
            }
            list = JdbcUtil.queryListLowercase(conn,sql.toString());
        }
        return dbTableDataForm.setData(PageUtil.getListPage((int) dbTableDataForm.getCurrentPage(), (int) dbTableDataForm.getPageSize(), list), list.size());
    }

    @Override
    public int getSum(String dbId,String table)throws DataException{
        int sum = 0;
        try {
            DbConnDTO connDTO = setResource(dbId);
            @Cleanup Connection conn = connDTO.getConn();
            if (conn != null) {
                String sql = DbKingbase.DATA_SUM_SQL.replace(DbSttEnum.TABLE.getTarget(),table);
                sum = JdbcUtil.queryOneInt(conn,sql,"COUNT_SUM");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sum;
    }

    /**=====================增删改========================**/

    @Override
    public void delete(String dbId, String table) throws DataException {
        try {
            DbConnDTO connDTO = setResource(dbId);
            @Cleanup Connection conn = connDTO.getConn();
            String sql = connDTO.getDbBase().getSqlBase().getDeleteSql(table);
            JdbcUtil.creUpDe(conn, sql);
        }catch (Exception e){
            throw new DataException(e.getMessage());
        }
    }

    @Override
    public int createTable(DbTableCreate dbTableCreate) throws DataException {
        try{
            String dbLink = dbTableCreate.getDbLinkId();
            DbConnDTO connDTO = setResource(dbLink);
            @Cleanup Connection conn = connDTO.getConn();
            SqlBase sqlBase = connDTO.getDbBase().getSqlBase();
            //表重名判断
            if (dbTableService.isExistByFullName(dbLink, dbTableCreate.getNewTable(), dbTableCreate.getNewTable())) {
                return 0;
            }
            //创建表
            checkPrimary(dbTableCreate.getDbTableFieldModelList());
            String createSql = sqlBase.getCreateSql(dbTableCreate.getNewTable(),dbTableCreate.getTableComment(),
                    dbTableCreate.getDbTableFieldModelList());
            JdbcUtil.creUpDe(conn, createSql);
        }catch (Exception e){
            throw new DataException(e.getMessage());
        }
        return 1;
    }


    @Override
    public void update(DbTableUpdate dbTableUpdate) throws DataException {
        try{
            DbConnDTO connDTO = setResource(dbTableUpdate.getDbLinkId());
            //临时表名
            String oldTable = dbTableUpdate.getOldTable();
            String signTableName = oldTable + "_" + CommonUtil.getStringRandom(5);
            SqlBase sqlBase = connDTO.getDbBase().getSqlBase();
            @Cleanup Connection conn = connDTO.getConn();
            try{
                //第一步：旧表改名（临时表名）
                JdbcUtil.creUpDe(conn, sqlBase.getRenameSql(oldTable, signTableName));
                //第二步：创建新表 (成功则删除旧表，失败则回滚)
                String createSql = sqlBase.getCreateSql(dbTableUpdate.getNewTable(), dbTableUpdate.getTableComment(),
                        dbTableUpdate.getDbTableFieldModelList());
                JdbcUtil.creUpDe(conn, createSql);
            }catch (Exception e){
                //旧表改回原名(回滚conn.rollback()无法滚回ALTER语句等DDL操作语句)
                JdbcUtil.creUpDe(conn, sqlBase.getRenameSql(signTableName, oldTable));
                throw e;
            }
            //手动提交
            conn.commit();
            //第三步：删除旧表
            JdbcUtil.creUpDe(conn,sqlBase.getDeleteSql(signTableName));
        }catch (Exception e){
            throw new DataException(e.getMessage());
        }
    }

    @Override
    public void getSyncTable(String lindId) {
        try{
            @Cleanup Connection conn = ConnUtil.getConnRemarks(dblinkService.getInfo(lindId));
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
    }

    /**================复用代码==================**/

    /**
     * 检查主键
     * @param tableFieldList
     * @throws DataException
     */
    private void checkPrimary(List<DbTableFieldModel> tableFieldList) throws DataException{
        //默认主键为字符串类型
        //主键会自动添加"非空"限制，所以不用做判断。(为空不加语句，且数据库默认字段可为空)
        for(DbTableFieldModel field : tableFieldList) {
            if (field.getPrimaryKey() == 1) {
                if(!field.getDataType().equals(ViewDataTypeConst.VARCHAR)){
                    throw new DataException("请修改主键为默认\"字符串\"类型。");
                }
            }
        }
    }

    /**
     * 设置数据源
     * @param linkId 数据连接id
     * @throws DataException ignore
     */
    private DbConnDTO setResource(String linkId) throws DataException{
        DataSourceUtil dbSourceOrDbLink;
        try {
            if("0".equals(linkId)){
                //多租户是否开启
                String tenSource = Boolean.parseBoolean(configValueUtil.getMultiTenancy()) ?
                        userProvider.get().getTenantDbConnectionString() : null;
                //默认数据库查询，从配置获取数据源信息
                dbSourceOrDbLink = dataSourceUtils;
                if(tenSource!=null){
                    dbSourceOrDbLink.setDbName(tenSource);
                }
            }else {
                dbSourceOrDbLink = dblinkService.getInfo(linkId);
            }
            return new DbConnDTO(DbTypeUtil.getDb(dbSourceOrDbLink),
                    dbSourceOrDbLink, ConnUtil.getConn(dbSourceOrDbLink));
        } catch (Exception e){
            throw new DataException(e.getMessage());
        }
    }


}
